package tt.dz.biz.dao.verifycode;

import java.util.List;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.LogUtil;
import tt.dz.vo.VerifyCodeVO;

@Repository("msVerifyCodeDAO")
public class MsVerifyCodeDAOImp
  extends BaseDAO
{
  private static final String QUERYBYPHONE_VERIFYCODE_SQL = "SELECT mobile_no,sms_type,content,send_time,over_time,is_valid,is_success,send_times  FROM ms_verify_code WHERE mobile_no=:mobileNo and now() between send_time and over_time   and is_valid='1' and is_success='1' order by send_time desc ";
  private static final String QUERYBYPHONE_ISSEND_SQL = "SELECT 1  FROM ms_verify_code WHERE mobile_no=:mobileNo and now() between send_time and send_time::timestamp + '1 min'   and is_valid='1' and is_success='1' ";
  private static final String VERIFYCODE_TO_INVALID = "update ms_verify_code set is_valid = 0 where mobile_no = :mobileNo and send_time = :sendTime and content = :content ";
  private static final String INSERT_VERIFYCODE_SQL = "insert into ms_verify_code(mobile_no,sms_type,content,send_time,over_time,is_valid,is_success,send_times)  VALUES(:mobileNo,:smsType,:content,:sendTime,:overTime,:isValid,:isSuccess,:sendTimes) ";
  
  public int insertVerifyCode(VerifyCodeVO msVerifyCodevo)
  {
    LogUtil.debug("开始插入短信验证相关数据,msVerifyCodevo=" + msVerifyCodevo);
    return save("insert into ms_verify_code(mobile_no,sms_type,content,send_time,over_time,is_valid,is_success,send_times)  VALUES(:mobileNo,:smsType,:content,:sendTime,:overTime,:isValid,:isSuccess,:sendTimes) ", msVerifyCodevo);
  }
  
  public VerifyCodeVO getVerifyCodeByPhone(String phone)
  {
    LogUtil.debug("短信验证码校验，手机号:" + phone);
    VerifyCodeVO param = new VerifyCodeVO();
    param.setMobileNo(phone);
    List<VerifyCodeVO> datalist = queryForList("SELECT mobile_no,sms_type,content,send_time,over_time,is_valid,is_success,send_times  FROM ms_verify_code WHERE mobile_no=:mobileNo and now() between send_time and over_time   and is_valid='1' and is_success='1' order by send_time desc ", param, VerifyCodeVO.class);
    if ((datalist != null) && (datalist.size() > 0))
    {
      LogUtil.debug("校验完全成，查询数据条数：" + datalist.size());
      return (VerifyCodeVO)datalist.get(0);
    }
    LogUtil.debug("校验完全成，未查询到有关验证码数据");
    return null;
  }
  
  public boolean isSendByPhone(String phone)
  {
    LogUtil.debug("短信验证码校验是否在1分钟内发送过，手机号:" + phone);
    VerifyCodeVO param = new VerifyCodeVO();
    param.setMobileNo(phone);
    List<VerifyCodeVO> datalist = queryForList("SELECT 1  FROM ms_verify_code WHERE mobile_no=:mobileNo and now() between send_time and send_time::timestamp + '1 min'   and is_valid='1' and is_success='1' ", param, VerifyCodeVO.class);
    if ((datalist != null) && (datalist.size() > 0))
    {
      LogUtil.debug("校验完全成，短信验证码在1分钟内发送过 ");
      return true;
    }
    LogUtil.debug("校验完全成，可以发送短信");
    return false;
  }
  
  public void updateVerifyCodeToInvalid(VerifyCodeVO vo)
  {
    LogUtil.debug("更新验证码信息 ， 有效 ==》无效；参数：" + vo.toString());
    save("update ms_verify_code set is_valid = 0 where mobile_no = :mobileNo and send_time = :sendTime and content = :content ", vo);
  }
}
